{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CH11 多表拼接（水果拼盘）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.1横向拼接"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在Excel中，表的横向拼接，多用Vlookup()函数实现，这是个非常常见的功能<br>\n",
    "而在Python中，利用merge()方法"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11.1.1连接表的类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   学号   成绩\n",
       "0   1  小张  100  650\n",
       "1   2  小王  101  600\n",
       "2   3  小李  102  578\n",
       "3   4  小赵  103  550"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict = {\"名次\":[1,2,3,4],\n",
    "             \"姓名\":[\"小张\",\"小王\",\"小李\",\"小赵\"],\n",
    "             \"学号\":[100,101,102,103],\n",
    "             \"成绩\":[650,600,578,550]}\n",
    "df1 = pd.DataFrame(data_dict)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>学号</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>102</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103</td>\n",
       "      <td>四班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    学号  班级\n",
       "0  100  一班\n",
       "1  101  二班\n",
       "2  102  三班\n",
       "3  103  四班"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict = {\"学号\":[100,101,102,103],\n",
    "             \"班级\":[\"一班\",\"二班\",\"三班\",\"四班\"]}\n",
    "df2 = pd.DataFrame(data_dict)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "      <td>四班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   学号   成绩  班级\n",
       "0   1  小张  100  650  一班\n",
       "1   2  小王  101  600  二班\n",
       "2   3  小李  102  578  三班\n",
       "3   4  小赵  103  550  四班"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1,df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "多对一，指待连接的两个表的公共列不是一对一的<br>\n",
    "但需要确保的是，一个表的公共列有重复值，而另一个表的公共列是唯一的<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>f_成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名   学号  f_成绩\n",
       "0  小张  100   650\n",
       "1  小王  101   600\n",
       "2  小李  102   578"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict = {\"姓名\":[\"小张\",\"小王\",\"小李\"],\n",
    "             \"学号\":[100,101,102],\n",
    "             \"f_成绩\":[650,600,578]}\n",
    "df1 = pd.DataFrame(data_dict)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>学号</th>\n",
       "      <th>e_成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "      <td>602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>102</td>\n",
       "      <td>645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>102</td>\n",
       "      <td>676</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    学号  e_成绩\n",
       "0  100   586\n",
       "1  100   602\n",
       "2  101   691\n",
       "3  101   702\n",
       "4  102   645\n",
       "5  102   676"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict = {\"学号\":[100,100,101,101,102,102],\n",
    "             \"e_成绩\":[586,602,691,702,645,676]}\n",
    "df2 = pd.DataFrame(data_dict)\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "由于df1中的学号是唯一的，而df2中的学号并非唯一<br>\n",
    "最终的拼接结果就是，保留df2中的重复值，且df1中也会相应增加重复值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>f_成绩</th>\n",
       "      <th>e_成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>676</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名   学号  f_成绩  e_成绩\n",
       "0  小张  100   650   586\n",
       "1  小张  100   650   602\n",
       "2  小王  101   600   691\n",
       "3  小王  101   600   702\n",
       "4  小李  102   578   645\n",
       "5  小李  102   578   676"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on = \"学号\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "多对多，指待连接的两个表的公共列都不是一对一的<br>\n",
    "这样，在拼接之后，就会出现笛卡尔乘积（M×N）的情况<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>f_成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>542</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名   学号  f_成绩\n",
       "0  小张  100   650\n",
       "1  小张  100   610\n",
       "2  小王  101   600\n",
       "3  小李  102   578\n",
       "4  小李  102   542"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict = {\"姓名\":[\"小张\",\"小张\",\"小王\",\"小李\",\"小李\"],\n",
    "             \"学号\":[100,100,101,102,102],\n",
    "             \"f_成绩\":[650,610,600,578,542]}\n",
    "df1 = pd.DataFrame(data_dict)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>学号</th>\n",
       "      <th>e_成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "      <td>610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>102</td>\n",
       "      <td>542</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    学号  e_成绩\n",
       "0  100   650\n",
       "1  100   610\n",
       "2  101   600\n",
       "3  102   578\n",
       "4  102   542"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_dict = {\"学号\":[100,100,101,102,102],\n",
    "             \"e_成绩\":[650,610,600,578,542]}\n",
    "df2 = pd.DataFrame(data_dict)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>f_成绩</th>\n",
       "      <th>e_成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>610</td>\n",
       "      <td>650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>610</td>\n",
       "      <td>610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>542</td>\n",
       "      <td>578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>542</td>\n",
       "      <td>542</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   姓名   学号  f_成绩  e_成绩\n",
       "0  小张  100   650   650\n",
       "1  小张  100   650   610\n",
       "2  小张  100   610   650\n",
       "3  小张  100   610   610\n",
       "4  小王  101   600   600\n",
       "5  小李  102   578   578\n",
       "6  小李  102   578   542\n",
       "7  小李  102   542   578\n",
       "8  小李  102   542   542"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11.1.2连接键的类型"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在merge()方法中，如果没有事先指定按哪个列进行拼接时，会默认寻找两个表中的公共列（相同的字段名），进行连接<br>\n",
    "也可以使用on方法，指定公共列或是一组公共列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict = {\"名次\":[1,2,3,4],\n",
    "             \"姓名\":[\"小张\",\"小王\",\"小李\",\"小赵\"],\n",
    "             \"学号\":[100,101,102,103],\n",
    "             \"成绩\":[650,600,578,550]}\n",
    "df1 = pd.DataFrame(data_dict)\n",
    "data_dict = {\"学号\":[100,101,102,103],\n",
    "             \"班级\":[\"一班\",\"二班\",\"三班\",\"四班\"]}\n",
    "df2 = pd.DataFrame(data_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "      <td>四班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   学号   成绩  班级\n",
       "0   1  小张  100  650  一班\n",
       "1   2  小王  101  600  二班\n",
       "2   3  小李  102  578  三班\n",
       "3   4  小赵  103  550  四班"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on = \"学号\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict = {\"名次\":[1,2,3,4],\n",
    "             \"姓名\":[\"小张\",\"小王\",\"小李\",\"小赵\"],\n",
    "             \"学号\":[100,101,102,103],\n",
    "             \"成绩\":[650,600,578,550]}\n",
    "df1 = pd.DataFrame(data_dict)\n",
    "data_dict = {\"姓名\":[\"小张\",\"小王\",\"小李\",\"小赵\"],\n",
    "             \"学号\":[100,101,102,103],\n",
    "             \"班级\":[\"一班\",\"二班\",\"三班\",\"四班\"]}\n",
    "df2 = pd.DataFrame(data_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "      <td>四班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   学号   成绩  班级\n",
       "0   1  小张  100  650  一班\n",
       "1   2  小王  101  600  二班\n",
       "2   3  小李  102  578  三班\n",
       "3   4  小赵  103  550  四班"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on = [\"姓名\",\"学号\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如遇到公共列，但字段名不同，则需要使用left_on与right_on参数手动添加"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict = {\"名次\":[1,2,3,4],\n",
    "             \"姓名\":[\"小张\",\"小王\",\"小李\",\"小赵\"],\n",
    "             \"编号\":[100,101,102,103],\n",
    "             \"成绩\":[650,600,578,550]}\n",
    "df1 = pd.DataFrame(data_dict)\n",
    "data_dict = {\"学号\":[100,101,102,103],\n",
    "             \"班级\":[\"一班\",\"二班\",\"三班\",\"四班\"]}\n",
    "df2 = pd.DataFrame(data_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>编号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>学号</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>100</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>101</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>102</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "      <td>103</td>\n",
       "      <td>四班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   编号   成绩   学号  班级\n",
       "0   1  小张  100  650  100  一班\n",
       "1   2  小王  101  600  101  二班\n",
       "2   3  小李  102  578  102  三班\n",
       "3   4  小赵  103  550  103  四班"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, left_on = \"编号\", right_on = \"学号\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "当公共列是索引时，就要把索引当作连接键，分别使用left_index = True与right_index = True参数来进行控制"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict = {\"名次\":[1,2,3,4],\n",
    "             \"姓名\":[\"小张\",\"小王\",\"小李\",\"小赵\"],\n",
    "             \"编号\":[100,101,102,103],\n",
    "             \"成绩\":[650,600,578,550]}\n",
    "df1 = pd.DataFrame(data_dict)\n",
    "df1.set_index(\"编号\",inplace = True)\n",
    "data_dict = {\"学号\":[100,101,102,103],\n",
    "             \"班级\":[\"一班\",\"二班\",\"三班\",\"四班\"]}\n",
    "df2 = pd.DataFrame(data_dict)\n",
    "df2.set_index(\"学号\", inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>成绩</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>编号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>650</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>600</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>578</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>550</td>\n",
       "      <td>四班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     名次  姓名   成绩  班级\n",
       "编号                  \n",
       "100   1  小张  650  一班\n",
       "101   2  小王  600  二班\n",
       "102   3  小李  578  三班\n",
       "103   4  小赵  550  四班"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, left_index = True, right_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "当然，公共列一边为索引列，一边为普通列也可以进行混用，具体操作方法为<br>\n",
    "left_index = True, right_on = \"学号\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict = {\"名次\":[1,2,3,4],\n",
    "             \"姓名\":[\"小张\",\"小王\",\"小李\",\"小赵\"],\n",
    "             \"编号\":[100,101,102,103],\n",
    "             \"成绩\":[650,600,578,550]}\n",
    "df1 = pd.DataFrame(data_dict)\n",
    "df1.set_index(\"编号\",inplace = True)\n",
    "data_dict = {\"学号\":[100,101,102,103],\n",
    "             \"班级\":[\"一班\",\"二班\",\"三班\",\"四班\"]}\n",
    "df2 = pd.DataFrame(data_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名</th>\n",
       "      <th>成绩</th>\n",
       "      <th>学号</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>650</td>\n",
       "      <td>100</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>600</td>\n",
       "      <td>101</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>578</td>\n",
       "      <td>102</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>550</td>\n",
       "      <td>103</td>\n",
       "      <td>四班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次  姓名   成绩   学号  班级\n",
       "0   1  小张  650  100  一班\n",
       "1   2  小王  600  101  二班\n",
       "2   3  小李  578  102  三班\n",
       "3   4  小赵  550  103  四班"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, left_index = True, right_on = \"学号\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11.1.3连接方式"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "之前的举例比较标准，即左表中的公共列的值在右表对应的公共列都能找到，右表中的公共列的值在左表对应的公共列都能找到<br>\n",
    "非常理想的情况，但在实际工作中多半不可能有这样的好事<br>\n",
    "这个时候，就可以用到参数how来指明连接方式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict = {\"名次\":[1,2,3,4],\n",
    "             \"姓名\":[\"小张\",\"小王\",\"小李\",\"小赵\"],\n",
    "             \"学号\":[100,101,102,103],\n",
    "             \"成绩\":[650,600,578,550]}\n",
    "df1 = pd.DataFrame(data_dict)\n",
    "data_dict = {\"姓名\":[\"小张\",\"小王\",\"小李\",\"小钱\"],\n",
    "             \"学号\":[100,101,102,104],\n",
    "             \"班级\":[\"一班\",\"一班\",\"二班\",\"三班\"]}\n",
    "df2 = pd.DataFrame(data_dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "通过如下例子，也可以看到，how的默认值为inner"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>小张</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>小王</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>小李</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次 姓名_x   学号   成绩 姓名_y  班级\n",
       "0   1   小张  100  650   小张  一班\n",
       "1   2   小王  101  600   小王  一班\n",
       "2   3   小李  102  578   小李  二班"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on = \"学号\", how = \"inner\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "除了默认的inner内连接，常见的连接方式还有如下几种<br>\n",
    "1.Left，左表为基础，右边往左表上拼接，没有匹配到的用NaN表示<br>\n",
    "2.Right，右表为基础，左边往左表上拼接，没有匹配到的用NaN表示<br>\n",
    "3.Outer，取两张表的并集<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>小张</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>小王</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>小李</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次 姓名_x   学号   成绩 姓名_y   班级\n",
       "0   1   小张  100  650   小张   一班\n",
       "1   2   小王  101  600   小王   一班\n",
       "2   3   小李  102  578   小李   二班\n",
       "3   4   小赵  103  550  NaN  NaN"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on = \"学号\", how = \"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650.0</td>\n",
       "      <td>小张</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600.0</td>\n",
       "      <td>小王</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578.0</td>\n",
       "      <td>小李</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>NaN</td>\n",
       "      <td>小钱</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    名次 姓名_x   学号     成绩 姓名_y  班级\n",
       "0  1.0   小张  100  650.0   小张  一班\n",
       "1  2.0   小王  101  600.0   小王  一班\n",
       "2  3.0   小李  102  578.0   小李  二班\n",
       "3  NaN  NaN  104    NaN   小钱  三班"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on = \"学号\", how = \"right\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650.0</td>\n",
       "      <td>小张</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600.0</td>\n",
       "      <td>小王</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578.0</td>\n",
       "      <td>小李</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>小赵</td>\n",
       "      <td>103</td>\n",
       "      <td>550.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104</td>\n",
       "      <td>NaN</td>\n",
       "      <td>小钱</td>\n",
       "      <td>三班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    名次 姓名_x   学号     成绩 姓名_y   班级\n",
       "0  1.0   小张  100  650.0   小张   一班\n",
       "1  2.0   小王  101  600.0   小王   一班\n",
       "2  3.0   小李  102  578.0   小李   二班\n",
       "3  4.0   小赵  103  550.0  NaN  NaN\n",
       "4  NaN  NaN  104    NaN   小钱   三班"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on = \"学号\", how = \"outer\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11.1.4重复列名处理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在两张表进行连接时，遇到列名重复，merge方法会自动添加后缀_x、_y或_z<br>\n",
    "不过我们也可以使用suffixes参数进行调整"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict = {\"名次\":[1,2,3,4],\n",
    "             \"姓名\":[\"小张\",\"小王\",\"小李\",\"小赵\"],\n",
    "             \"学号\":[100,101,102,103],\n",
    "             \"成绩\":[650,600,578,550]}\n",
    "df1 = pd.DataFrame(data_dict)\n",
    "data_dict = {\"姓名\":[\"小张\",\"小王\",\"小李\",\"小钱\"],\n",
    "             \"学号\":[100,101,102,104],\n",
    "             \"班级\":[\"一班\",\"一班\",\"二班\",\"三班\"]}\n",
    "df2 = pd.DataFrame(data_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名_x</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>姓名_y</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>小张</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>小王</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>小李</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次 姓名_x   学号   成绩 姓名_y  班级\n",
       "0   1   小张  100  650   小张  一班\n",
       "1   2   小王  101  600   小王  一班\n",
       "2   3   小李  102  578   小李  二班"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on = \"学号\", how = \"inner\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>名次</th>\n",
       "      <th>姓名_L</th>\n",
       "      <th>学号</th>\n",
       "      <th>成绩</th>\n",
       "      <th>姓名_R</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>小张</td>\n",
       "      <td>100</td>\n",
       "      <td>650</td>\n",
       "      <td>小张</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>小王</td>\n",
       "      <td>101</td>\n",
       "      <td>600</td>\n",
       "      <td>小王</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>小李</td>\n",
       "      <td>102</td>\n",
       "      <td>578</td>\n",
       "      <td>小李</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   名次 姓名_L   学号   成绩 姓名_R  班级\n",
       "0   1   小张  100  650   小张  一班\n",
       "1   2   小王  101  600   小王  一班\n",
       "2   3   小李  102  578   小李  二班"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on = \"学号\", how = \"inner\", suffixes = [\"_L\",\"_R\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.2表的纵向拼接"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "纵向拼接是指两张表在垂直的方向进行拼接<br>\n",
    "一般的使用场景是将分离的若干个结构完全相同的表合并成一张数据表<br>\n",
    "在Excel中，实现合并，仅需要将表二左复制粘贴操作，在Python中，需要用到concat()方法"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11.2.1普通合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict = {\"编号\":[1,2,3,4],\n",
    "             \"姓名\":[\"许丹\",\"李旭文\",\"程成\",\"赵涛\"],\n",
    "             \"班级\":[\"一班\",\"一班\",\"一班\",\"一班\"]}\n",
    "df1 = pd.DataFrame(data_dict)\n",
    "df1.set_index(\"编号\", inplace = True)\n",
    "data_dict = {\"编号\":[1,2,3,4],\n",
    "             \"姓名\":[\"赵义\",\"李鹏\",\"卫来\",\"葛彦\"],\n",
    "             \"班级\":[\"二班\",\"二班\",\"二班\",\"二班\"]}\n",
    "df2 = pd.DataFrame(data_dict)\n",
    "df2.set_index(\"编号\", inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>编号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>许丹</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>李旭文</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>程成</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>赵涛</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>赵义</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>李鹏</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>卫来</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>葛彦</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     姓名  班级\n",
       "编号         \n",
       "1    许丹  一班\n",
       "2   李旭文  一班\n",
       "3    程成  一班\n",
       "4    赵涛  一班\n",
       "1    赵义  二班\n",
       "2    李鹏  二班\n",
       "3    卫来  二班\n",
       "4    葛彦  二班"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1,df2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11.2.2索引设置"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "concat()方法默认保留原表的索引，正如上例，合并后的索引变成了1234 1234，不符合我们对表的通常认知<br>\n",
    "可以通过ignore_index = True，生成一组新的索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>许丹</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李旭文</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>程成</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>赵涛</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>赵义</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>李鹏</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>卫来</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>葛彦</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    姓名  班级\n",
       "0   许丹  一班\n",
       "1  李旭文  一班\n",
       "2   程成  一班\n",
       "3   赵涛  一班\n",
       "4   赵义  二班\n",
       "5   李鹏  二班\n",
       "6   卫来  二班\n",
       "7   葛彦  二班"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1,df2], ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11.2.3重叠数据合并"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在现实业务中，难免会有一些错误数据，比如说一班的名单中混进了二班的人，这个人在二班的名单中也出现了<br>\n",
    "因为生成的新表也同样为DataFrame格式，可以使用drop_duplicates方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dict = {\"编号\":[1,2,3,4,5],\n",
    "             \"姓名\":[\"许丹\",\"李旭文\",\"程成\",\"赵涛\",\"葛彦\"],\n",
    "             \"班级\":[\"一班\",\"一班\",\"一班\",\"一班\",\"二班\"]}\n",
    "df1 = pd.DataFrame(data_dict)\n",
    "df1.set_index(\"编号\", inplace = True)\n",
    "data_dict = {\"编号\":[1,2,3,4],\n",
    "             \"姓名\":[\"赵义\",\"李鹏\",\"卫来\",\"葛彦\"],\n",
    "             \"班级\":[\"二班\",\"二班\",\"二班\",\"二班\"]}\n",
    "df2 = pd.DataFrame(data_dict)\n",
    "df2.set_index(\"编号\", inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>姓名</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>许丹</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>李旭文</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>程成</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>赵涛</td>\n",
       "      <td>一班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>葛彦</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>赵义</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>李鹏</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>卫来</td>\n",
       "      <td>二班</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    姓名  班级\n",
       "0   许丹  一班\n",
       "1  李旭文  一班\n",
       "2   程成  一班\n",
       "3   赵涛  一班\n",
       "4   葛彦  二班\n",
       "5   赵义  二班\n",
       "6   李鹏  二班\n",
       "7   卫来  二班"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1,df2], ignore_index = True).drop_duplicates()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
